package be.kiojeux.dao.connection;

import org.apache.log4j.Logger;
import java.sql.*;

public class StoredProcedures {
	private static final Logger log = Logger.getLogger(StoredProcedures.class);
	/**
	 * @param args
	 */
	public static void main(String[] args) {		
		
		//CHARGEMENT DU DRIVER
	
		try {				
			Class.forName("com.mysql.jdbc.Driver");
		} 
		catch (ClassNotFoundException e) {
			log.error("Chargement driver: Echec");
			e.printStackTrace();
		}
		
		//CONNECTION AU SGBD
		
		//String url = "jdbc:mysql://conceptled.dyndns.org/kiojeux";
		//String url = "jdbc:mysql://localhost:3306/kiojeux";
		//String user = "root";
		//String password = "";
		String url = "jdbc:mysql://instance31669.db.xeround.com:20447/kiojeux";
		String user = "kiojeux";
		String password = "KioJeux789321";
		Connection connection;
		try {
			connection = DriverManager.getConnection(url,user,password);
			log.debug("Connexion SGBD: Ok");
			
			//CREATION DES PROCEDURES
			
			findUserLastNameFirstName(connection);				
			findOtherUserCard(connection);
			findUser(connection);
			addUser(connection);
			modifUser(connection);
			deleteUser(connection);			
			subscriptionPrices(connection);
			findSubscription(connection);
			modifySubscription(connection);
			findUserReservation(connection);
			findGameReservation(connection);
			createOnceSubscription(connection);
			createCardSubscription(connection);
			createAnnualSubscription(connection);
			createOtherCardSubscription(connection);
			setOtherUserCardSubscriptionToZero(connection);
			updateOnceAndOtherCard(connection);
			updateCard(connection);
			updateCardZero(connection);
			updateAnnual(connection);
			treasuryNewEntry(connection);
			comboBar(connection);
			comboCardBar(connection);
			comboBarDelete(connection);
			findBar(connection);			
			addBar(connection);
			modifyBar(connection);
			deleteBar(connection);
			updateStockBar(connection);
			updateCardDrink(connection);
			comboTreasury(connection);
			
		} 
		catch (SQLException e) {
			log.error("Connexion SGBD: Echec");
			e.printStackTrace();
		}			
	}	
	
	public static void findUserLastNameFirstName(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_findUserLastNameFirstName");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_findUserLastNameFirstName()\n"
			+ "BEGIN\n"
			+ "SELECT idmembre, nom, prenom FROM membres ORDER BY nom ASC;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_findUserLastNameFirstName: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_findUserLastNameFirstName: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void findOtherUserCard(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_findOtherUserCard");			
			statement.executeUpdate(
			"CREATE PROCEDURE SP_findOtherUserCard(IN idSubscription VARCHAR(50))\n"
			+ "BEGIN\n"
			+ "SELECT idMembre, nom, prenom, resteCarteAbonnement FROM membres WHERE idAbonnement = idSubscription ORDER BY nom ASC;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_findOtherUserCard: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_findOtherUserCard: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void findUser(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_findUser");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_findUser(IN idUser INT)\n"
			+ "BEGIN\n"
			+ "SELECT * FROM membres WHERE idmembre = idUser;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_findUser: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_findUser: Echec");
			e.printStackTrace();
		}						
	}
			
	public static void addUser(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_addUser");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_addUser(IN lname VARCHAR(50), IN fname VARCHAR(50),"
			+ "IN birthdate VARCHAR(50), IN mail VARCHAR(50), IN phone VARCHAR(50),"
			+ "IN address VARCHAR(50), IN number VARCHAR(50), IN box VARCHAR(50), IN zipCode VARCHAR(50), IN city VARCHAR(50),"
			+ "IN log VARCHAR(50), IN pwd VARCHAR(50), IN remarks VARCHAR(50))\n"
			+ "BEGIN\n"
			+ "INSERT INTO membres (nom, prenom, datedenaissance, email, gsm, adresse, numero, boite, codePostal, ville, login, pwd, remarques)"
			+ "VALUES (lname, fname, birthdate, mail, phone, address, number, box, zipCode, city, log, pwd, remarks);\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_addUser: Ok");
		} catch (SQLException e) {
			log.error("Procedure SP_addUser: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void modifUser(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_modifUser");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_modifUser(IN lname VARCHAR(50), IN fname VARCHAR(50),"
			+ " IN active VARCHAR(50), IN idSubscription VARCHAR(50), IN dateSubscription VARCHAR(50), IN restCardSubscription VARCHAR(50),"		
			+ " IN restCardDrink VARCHAR(50), IN birthdate VARCHAR(50), IN mail VARCHAR(50), IN phone VARCHAR(50),"
			+ " IN address VARCHAR(50), IN number VARCHAR(50), IN box VARCHAR(50), IN zipCode VARCHAR(50), IN city VARCHAR(50),"
			+ " IN log VARCHAR(50), IN pwd VARCHAR(50), IN remarks VARCHAR(50), IN userId INT)\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET nom=lname, prenom=fname, actif=active, idAbonnement=idSubscription, dateAbonnement=dateSubscription,"
			+ " resteCarteAbonnement=restCardSubscription, resteCarteBoisson=restCardDrink, datedenaissance=birthdate, email=mail, gsm=phone,"
			+ " adresse=address, numero=number, boite=box, codePostal=zipCode, ville=city, login=log, pwd=pwd, remarques=remarks\n"
			+ "WHERE idmembre=userId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_modifUser: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_modifUser: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void deleteUser(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_deleteUser");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_deleteUser(IN userId INT)\n"
			+ "BEGIN\n"
			+ "DELETE FROM membres WHERE idmembre=userId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_deleteUser: Ok");
		} catch (SQLException e) {
			log.error("Procedure SP_deleteUser: Echec");
			e.printStackTrace();
		}						
	}
								
	public static void subscriptionPrices(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_subscriptionPrices");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_subscriptionPrices()\n"
			+ "BEGIN\n"
			+ "SELECT * FROM abonnements WHERE idabonnement > 0;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_subscriptionPrices: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_subscriptionPrices: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void findSubscription(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_findSubscription");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_findSubscription(IN idSub INT)\n"
			+ "BEGIN\n"
			+ "SELECT * FROM abonnements WHERE idabonnement=idSub;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_findSubscription: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_findSubscription: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void modifySubscription(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_modifySubscription");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_modifySubscription(IN type VARCHAR(50), IN price FLOAT, IN id INT)\n"
			+ "BEGIN\n"
			+ "UPDATE abonnements SET typeabonnement=type, prixabonnement=price\n"
			+ "WHERE idabonnement=id;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_modifySubscription: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_modifySubscription: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void findUserReservation(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_findUserReservation");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_findUserReservation(IN userId INT)\n"
			+ "BEGIN\n"
			+ "SELECT * FROM joue Where idmembre = userId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_findUserReservation: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_findUserReservation: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void findGameReservation(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_findGameReservation");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_findGameReservation(IN gameId INT)\n"
			+ "BEGIN\n"
			+ "SELECT denomination FROM jeux Where idjeu = gameId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_findGameReservation: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_findGameReservation: Echec");
			e.printStackTrace();
		}						
	}

	public static void createOnceSubscription(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_createOnceSubscription");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_createOnceSubscription(IN userId INT, IN userSubscription INT)\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET idAbonnement=userSubscription WHERE idmembre=userId;\n"			
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_createOnceSubscription: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_createOnceSubscription: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void createCardSubscription(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_createCardSubscription");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_createCardSubscription(IN userId INT, IN userSubscription INT)\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET idAbonnement=userSubscription, resteCarteAbonnement=10 WHERE idmembre=userId;\n"			
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_createCardSubscription: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_createCardSubscription: Echec");
			e.printStackTrace();
		}						
	}
		
	public static void createAnnualSubscription(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_createAnnualSubscription");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_createAnnualSubscription(IN userId INT, IN userSubscription INT, IN dateSubscription DATE)\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET idAbonnement=userSubscription, dateAbonnement=dateSubscription WHERE idmembre=userId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_createAnnualSubscription: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_createAnnualSubscription: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void createOtherCardSubscription(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_createOtherCardSubscription");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_createOtherCardSubscription(IN userId INT, IN userSubscription INT, IN idOtherUserCard INT)\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET idAbonnement=userSubscription WHERE idmembre=userId;\n"
			+ "UPDATE membres SET resteCarteAbonnement=resteCarteAbonnement-1 where idmembre=idOtherUserCard;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_createOtherCardSubscription: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_createOtherCardSubscription: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void setOtherUserCardSubscriptionToZero(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_setOtherUserCardSubscriptionToZero");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_setOtherUserCardSubscriptionToZero(IN idOtherUserCard INT)\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET idAbonnement=0 WHERE idmembre=idOtherUserCard;\n"			
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_setOtherUserCardSubscriptionToZero: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_setOtherUserCardSubscriptionToZero: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void updateOnceAndOtherCard(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_updateOnceAndOtherCard");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_updateOnceAndOtherCard(IN userId INT, IN remarks VARCHAR(50))\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET idAbonnement=0, remarques=remarks WHERE idmembre=userId;\n"			
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_updateOnceAndOtherCard: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_updateOnceAndOtherCard: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void updateCard(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_updateCard");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_updateCard(IN userId INT, IN remarks VARCHAR(50))\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET resteCarteAbonnement=resteCarteAbonnement-1, remarques=remarks WHERE idmembre=userId;\n"			
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_updateCard: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_updateCard: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void updateCardZero(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_updateCardZero");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_updateCardZero(IN userId INT, IN remarks VARCHAR(50))\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET resteCarteAbonnement=resteCarteAbonnement-1, idAbonnement=0, remarques=remarks WHERE idmembre=userId;\n"			
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_updateCardZero: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_updateCardZero: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void updateAnnual(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_updateAnnual");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_updateAnnual(IN userId INT, IN remarks VARCHAR(50))\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET remarques=remarks WHERE idmembre=userId;\n"			
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_updateAnnual: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_updateAnnual: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void treasuryNewEntry(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_treasuryNewEntry");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_treasuryNewEntry(IN transaction INT, IN denomination VARCHAR(50), "
			+ "IN quantity INT, IN price VARCHAR(50), IN date VARCHAR(50), IN remarks VARCHAR(50))\n"
			+ "BEGIN\n"
			+ "INSERT INTO tresorerie (transaction, denomination, quantite, prix, date, remarques) "
			+ "VALUES (transaction, denomination, quantity, price, date, remarks);\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_treasuryNewEntry: Ok");
		} catch (SQLException e) {
			log.error("Procedure SP_treasuryNewEntry: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void comboBar(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_comboBar");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_comboBar()\n"
			+ "BEGIN\n"
			+ "SELECT * FROM articlesbar ORDER BY denomination ASC;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_comboBar: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_comboBar: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void comboCardBar(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_comboCardBar");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_comboCardBar()\n"
			+ "BEGIN\n"
			+ "SELECT idMembre, nom, prenom, resteCarteBoisson FROM membres WHERE resteCarteBoisson > 0 ORDER BY nom ASC;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_comboCardBar: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_comboCardBar: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void comboBarDelete(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_comboBarDelete");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_comboBarDelete()\n"
			+ "BEGIN\n"
			+ "SELECT * FROM articlesbar where stock=0 ORDER BY denomination ASC;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_comboBarDelete: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_comboBarDelete: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void findBar(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_findBar");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_findBar(IN idBar INT)\n"
			+ "BEGIN\n"
			+ "SELECT * FROM articlesbar WHERE idarticle = idBar;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_findBar: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_findBar: Echec");
			e.printStackTrace();
		}						
	}
				
	public static void addBar(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_addBar");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_addBar(IN denomination VARCHAR(50), "
			+ "IN price FLOAT, IN stock INT)\n"
			+ "BEGIN\n"
			+ "INSERT INTO articlesbar (denomination, prix, stock) "
			+ "VALUES (denomination, price, stock);\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_addBar: Ok");
		} catch (SQLException e) {
			log.error("Procedure SP_addBar: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void modifyBar(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_modifyBar");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_modifyBar(IN denomination VARCHAR(50), "			
			+ "IN price FLOAT, IN stock INT, IN barId INT)\n"
			+ "BEGIN\n"
			+ "UPDATE articlesbar SET denomination=denomination, prix=price, stock=stock \n"
			+ "WHERE idarticle=barId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_modifyBar: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_modifyBar: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void deleteBar(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_deleteBar");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_deleteBar(IN barId INT)\n"
			+ "BEGIN\n"
			+ "DELETE FROM articlesbar WHERE idarticle=barId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_deleteBar: Ok");
		} catch (SQLException e) {
			log.error("Procedure SP_deleteBar: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void updateStockBar(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_updateStockBar");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_updateStockBar(IN stock INT, IN barId INT)\n"
			+ "BEGIN\n"
			+ "UPDATE articlesbar SET stock=stock \n"
			+ "WHERE idarticle=barId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_updateStockBar: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_updateStockBar: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void updateCardDrink(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_updateCardDrink");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_updateCardDrink(IN cardDrink INT, IN userId INT)\n"
			+ "BEGIN\n"
			+ "UPDATE membres SET restecarteboisson=cardDrink \n"
			+ "WHERE idmembre=userId;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_updateCardDrink: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_updateCardDrink: Echec");
			e.printStackTrace();
		}						
	}
	
	public static void comboTreasury(Connection connection){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("DROP PROCEDURE IF EXISTS SP_comboTreasury");
			statement.executeUpdate(
			"CREATE PROCEDURE SP_comboTreasury()\n"
			+ "BEGIN\n"
			+ "SELECT * FROM tresorerie ORDER BY date DESC;\n"
			+ "END\n");
			statement = null;
			log.debug("Procedure SP_comboTreasury: OK");
		} catch (SQLException e) {
			log.error("Procedure SP_comboTreasury: Echec");
			e.printStackTrace();
		}						
	}
		
}
